﻿## Paths
cls
$pathToEvExports = "\\chw-ev-003\e$\Exports\ArchiveReports"
$pathForReport = "E:\EVArchiveData\AuditReports"
$nameForReport = "evOrphanedArchives-$(get-date -format "yyyyMMdd-hhmmss")"

## Function that imports the TSV files output by EV.
function Import-TabDelimited ($Path) {
	$skiplines = 3  #Number of top lines to skip
	$filecheck = Get-Content $Path
	$totalLines = $filecheck.count
	$filecheck = $null
  Get-Content $path |ForEach-Object {$header = $true; $line = 1}  {
  	if ($line -le $skiplines) {}
    elseif ($header){
      $h = $_.split("`t")
      $header = $false
    }
    Else {
		$percent = ($line/ $totalLines) * 100
		Write-Progress -Activity "Importing report file" -Status "% Complete" -PercentComplete $percent -Id 1
      $r = new-object object
      $_.split("`t") |ForEach-Object {$i=0}{
        $r | add-Member -memberType noteProperty -name $h[$i] -value $_
        $i++
      }
      $r 
    }
	$line += 1
  }
}


$ev = $Null
$ev = (1..4) | ForEach-Object { Write-Progress -Activity "Loading EV Archive Data" -CurrentOperation "Importing EV Server $($_) data" -Status "% Complete" -PercentComplete ($_ * 25) -Id 0 ; `
 Get-ChildItem "$($pathToEvExports)\EV_VN_EVMBAVS0$($_)*" | Sort-Object lastwritetime | Select-Object -last 1} | `
 ForEach-Object {Import-TabDelimited $_} | Where-Object {$_."Billing Account" -like "S-*"} | `
 Select-Object "Archive Name",@{Name="Displayname";Expression={$_."Archive Name"}},"Billing Account","Total Items","Total Size (MB)","Archive Description"
 
$disc03 = ("301","302","303","401","402","403") | ForEach-Object {$step=0} {Write-Progress -Activity "Getting 2003 disconnected mailboxes" -Status "% Complete" -PercentComplete (($step / 6) * 100) -Id 2 ; `
 Get-Wmiobject -namespace root\MicrosoftExchangeV2 -class Exchange_Mailbox -computer "chw-msg-$($_)" -filter "DateDiscoveredAbsentInDS is not null"; $step += 1 } | `
 Select-Object @{Name="DisconnectDate";Expression={[Management.ManagementDateTimeConverter]::ToDateTime($_.DateDiscoveredAbsentInDS)}},@{Name="TotalDeletedItemSize";Expression={$_.DeletedMessageSizeExtended}},LastLoggedOnUserAccount,@{Name="LastLogonTime";Expression={[Management.ManagementDateTimeConverter]::ToDateTime($_.LastLogonTime)}},@{Name="LastLogoffTime";Expression={[Management.ManagementDateTimeConverter]::ToDateTime($_.LastLogoffTime)}},LegacyDN,@{Name="DisplayName";Expression={$_.MailboxDisplayName}},ServerName,@{Name="TotalItemSize";Expression={$_.Size}},StorageGroupName,@{Name="DatabaseName";Expression={$_.StoreName}},@{Name="ItemCount";Expression={$_.TotalItems}}
$disc07 = ("011","821","822","823","824") | ForEach-Object {$step=0} {Write-Progress -Activity "Getting 2007 disconnected mailboxes"  -Status "% Complete" -PercentComplete (($step / 5) * 100) -Id 3 ; `
 Get-MailboxStatistics -server "chw-msg-$($_)" ;$step += 1} | Where-Object {$_.DisconnectDate -ne $null} | `
 Select-Object DisconnectDate,TotalDeletedItemSize,LastLoggedOnUserAccount,LastLogonTime,LastLogoffTime,LegacyDN,DisplayName,ServerName,TotalItemSize,StorageGroupName,DatabaseName,ItemCount
$discMailboxes = $disc03 + $disc07

$evOrphans = Compare-Object $ev $discMailboxes -Property Displayname -IncludeEqual -PassThru | Where-Object {$_.SideIndicator -eq "<=" -or $_.SideIndicator -eq "=="} | `
 Select-Object Displayname,@{Name="Disconnected Mailbox Exists";Expression={if ($_.SideIndicator -eq "==") {"Yes"} else {"No"}}},@{Name="SID";Expression={$_."Billing Account"}},"Total Items","Total Size (MB)","Archive Description" 

$evOrphans = $evOrphans | Sort-Object Displayname

$evOrphans | Export-Csv -NoTypeInformation "$($pathForReport)\$($nameForReport).csv"


##
## Save all this out to Excel too and try to prettify it some.
##

## The constants that we will use

$xlCenter = -4108
$xlCellValue = 1
$xlEqual = 3
$xl3TrafficLights2 = 5
$xl3Symbols = 7
$xlConditionValueNumber = 0
$xlIconSet = 6
$xlAutomatic = -4105
$xlBottom = -4107
$xlContinuous = 1
$xlThin = 2

# Create an instance of Excel
$Excel = New-Object -comobject Excel.Application
$Excel.visible = $True 
$Workbook = $Excel.Workbooks.Add()
$Info = $Workbook.Worksheets.Item(1)
  
# Create our column headers
$Info.Cells.Item(1,1) = "Displayname"
$Info.Cells.Item(1,2) = "Disconnected Mailbox Exists"
$Info.Cells.Item(1,3) = "SID"
$Info.Cells.Item(1,4) = "Total Items"
$Info.Cells.Item(1,5) = "Total Size (MB)"
$Info.Cells.Item(1,6) = "Archive Description"

# Make Columns wide enough for content
$info.Range("A:A").entireColumn.ColumnWidth = 28
$info.Range("B:B").entireColumn.ColumnWidth = 25
$info.Range("B:B").HorizontalAlignment = $xlCenter 
$info.Range("C:C").entireColumn.ColumnWidth = 61
$info.Range("D:D").entireColumn.ColumnWidth = 10
$info.Range("E:E").entireColumn.ColumnWidth = 13
$info.Range("F:F").entireColumn.ColumnWidth = 39

# Add a little formatting to the column header
$Style = $Info.UsedRange
$Style.Interior.ColorIndex = 19
$Style.Font.ColorIndex = 11
$Style.Font.Bold = $True
$Style.Borders.LineStyle = $xlContinuous
$Style.Borders.ColorIndex = 0
$Style.Borders.TintAndShade = 0
$Style.Borders.Weight = $xlThin

$bucket= $Info.Range("B:B").FormatConditions.Add($xlCellValue, $xlEqual, '="No"')
$Info.Range("B:B").FormatConditions.Item(1).Interior.Color = 13551615
$Info.Range("B:B").FormatConditions.Item(1).Interior.PatternColorIndex = $xlAutomatic
$Info.Range("B:B").FormatConditions.Item(1).Interior.TintAndShade = 0
$Info.Range("B:B").FormatConditions.Item(1).Font.Color = -16383844
$Info.Range("B:B").FormatConditions.Item(1).Font.TintAndShade = 0


$bucket = $Info.Range("B:B").FormatConditions.Add($xlCellValue, $xlEqual, '="Yes"')
$Info.Range("B:B").FormatConditions.Item(2).Interior.Color = 13561798
$Info.Range("B:B").FormatConditions.Item(2).Interior.PatternColorIndex = $xlAutomatic
$Info.Range("B:B").FormatConditions.Item(2).Interior.TintAndShade = 0
$Info.Range("B:B").FormatConditions.Item(2).Font.Color = -16752384
$Info.Range("B:B").FormatConditions.Item(2).Font.TintAndShade = 0


# Data starts at row 2
$intRow = 2

# iterate over each Archive entry
foreach ($Archive in $evOrphans)
#for ($i = 0;$i -le 50; $i++) 
{
 #$Archive = $evOrphans[$i]
 $bucket = $Style.Cells.Item($intRow, 1).Select()
 $Style.Cells.Item($intRow, 1) = $Archive.DisplayName
 $Style.Cells.Item($intRow, 2) = $Archive."Disconnected Mailbox Exists" 
 $Style.Cells.Item($intRow, 3) = $Archive."SID" 
 $Style.Cells.Item($intRow, 4) = $Archive."Total Items" 
 $Style.Cells.Item($intRow, 5) = $Archive."Total Size (MB)"
 $Style.Cells.Item($intRow, 6) = $Archive."Archive Description" 
 
 $intRow++
}
$bucket = $Style.Cells.Item(1, 1).Select()
 

# Make Columns wide enough for content
$bucket = $info.Range("A:A").entireColumn.Autofit()
$bucket = $info.Range("B:B").entireColumn.Autofit()
$bucket = $info.Range("C:C").entireColumn.Autofit()
$bucket = $info.Range("D:D").entireColumn.Autofit()
$bucket = $info.Range("E:E").entireColumn.Autofit()
$bucket = $info.Range("F:F").entireColumn.Autofit()




if (Test-Path "$($pathForReport)\$($nameForReport).xlsx") {Remove-Item "$($pathForReport)\$($nameForReport).xlsx"}
$Workbook.SaveAs("$($pathForReport)\$($nameForReport).xlsx")
$Excel.Quit()


